package com.durablemethods.futures.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import com.durablemethods.futures.client.NHLTradeEntryDto;
import com.durablemethods.futures.client.NHLTrader;
import com.google.appengine.api.rdbms.AppEngineDriver;

public class StaticTraderPositionService {
	private static final String CONNECTION_STRING = "jdbc:google:rdbms://durablemethods.com:duravest:durable-futures-db";
	private Logger log = Logger.getLogger(StaticTraderPositionService.class.getName());
	private List<String> teams;
	
	public StaticTraderPositionService() {
		setTeams(new ArrayList<String>());
		getTeams().add("PIT");
		getTeams().add("CHI");
		getTeams().add("MON");
		getTeams().add("ANA");
		getTeams().add("WAS");
		getTeams().add("VAN");
		getTeams().add("STL");
		getTeams().add("BOS");
		getTeams().add("TOR");
		getTeams().add("LAK");
		getTeams().add("NYR");
		getTeams().add("SJ");
		getTeams().add("OTT");
		getTeams().add("DET");
		getTeams().add("NYI");
		getTeams().add("MIN");
	}

	public List<TraderPositionSummary> getPositionSummaries() {
		Connection c = null;
		Map<String, TraderPositionSummary> summaries = new HashMap<String, TraderPositionSummary>();
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("select t.ALIAS, TEAM_ID, sum(quantity) as TRADER_POSITION from durable_futures.TRADES tr, durable_futures.TRADERS t where tr.TRADER_ID = t.GOOGLE_ACCOUNT group by TRADER_ID, TEAM_ID order by TEAM_ID");
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				String trader = rs.getString("ALIAS");
				String team = rs.getString("TEAM_ID");
				Integer position = rs.getInt("TRADER_POSITION");
				if (summaries.containsKey(trader)){
					TraderPositionSummary traderPositionSummary = summaries.get(trader);
					traderPositionSummary.addTeamAndSize(team,position);
				}else{
					TraderPositionSummary traderPositionSummary = new TraderPositionSummary();
					traderPositionSummary.setTrader(trader);
					traderPositionSummary.addTeamAndSize(team, position);
					summaries.put(trader, traderPositionSummary);
				}
			}
			ps.close();
			c.close();
		} catch (SQLException e) {
			log.severe(e.getMessage());
			e.printStackTrace();
		}
		List<TraderPositionSummary> returnList = new ArrayList<TraderPositionSummary>(summaries.values());
		Collections.sort(returnList, new TraderPositionSummaryComparator());
		return returnList;
	}
	public TraderPositionSummary getPositionSummariesForTrader(String googleAccount) {
		Connection c = null;
		TraderPositionSummary traderPositionSummary = new TraderPositionSummary();
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			c = DriverManager.getConnection(CONNECTION_STRING,"root","");
			PreparedStatement ps = c.prepareStatement("select t.ALIAS, TEAM_ID, sum(quantity) as TRADER_POSITION from durable_futures.TRADES tr, durable_futures.TRADERS t where t.google_acount=? and  tr.TRADER_ID = t.GOOGLE_ACCOUNT group by TRADER_ID, TEAM_ID order by TEAM_ID");
			ps.setString(1, googleAccount);
			ResultSet rs = ps.executeQuery();
			while (rs.next()){
				String trader = rs.getString("ALIAS");
				String team = rs.getString("TEAM_ID");
				traderPositionSummary.setTrader(trader);
				Integer position = rs.getInt("TRADER_POSITION");
				traderPositionSummary.addTeamAndSize(team, position);
			}
			ps.close();
			c.close();
		} catch (SQLException e) {
			log.severe(e.getMessage());
			e.printStackTrace();
		}
		return traderPositionSummary;
	}
	

	public List<String> getTeams() {
		return teams;
	}

	public void setTeams(List<String> teams) {
		this.teams = teams;
	}
	

}
